#include "importtool.h"
#include "databases.h"
#include <QMessageBox>
#include <QAxObject>
#include <QFileDialog>
#include <QFile>
#include <QProgressDialog>
#include "qexcel.h"
#include <QDateTime>

ImportTool::ImportTool(QObject *parent) :
    QObject(parent)
{
    datadb = new Databases(this);
    QString results = datadb->openDatabase();

    if( !results.replace(" ","").isEmpty() )
    {
        QMessageBox msgBox( QMessageBox::Warning,QStringLiteral("提示"),
                            QStringLiteral("门禁数据库启动失败,请配置好数据库再试.Error:%2").arg(results),
                            QMessageBox::NoButton,
                            0
                            );
        msgBox.addButton(QStringLiteral("确定"), QMessageBox::AcceptRole);
        msgBox.exec();
        exit(0);
    }

    QString fileName = QFileDialog::getOpenFileName(0,
                                                    QStringLiteral("选择人员信息表"),
                                                    QStringLiteral("consumer.xlsx"),
                                                    QStringLiteral("**.xlsx .xls;;All Files (*))"));

    if( !fileName.isEmpty())
    {
        readExcel(fileName);
    }
    exit(0);
}
/*-------------------------------------------
 *
 * 读取excel内容
 *
---------------------------------------------*/
void ImportTool::readExcel(const QString &filepath)
{
    QAxObject *excel = NULL;
    QAxObject *workbooks = NULL;
    QAxObject *workbook = NULL;

    excel = new QAxObject("Excel.Application");
    if (!excel)
    {
        QMessageBox msgBox( QMessageBox::Warning,QStringLiteral("错误信息"),
                            QStringLiteral("EXCEL对象丢失，请重试"),
                            QMessageBox::NoButton,
                            0
                            );
        msgBox.addButton(QStringLiteral("确定"), QMessageBox::AcceptRole);
        msgBox.exec();
        exit(0);
    }
    excel->dynamicCall("SetVisible(bool)", false);
    workbooks = excel->querySubObject("WorkBooks");
    workbook = workbooks->querySubObject("Open(QString, QVariant)",filepath);
    QAxObject * worksheet = workbook->querySubObject("WorkSheets(int)", 1);                     //打开第一个sheet

    QAxObject * usedrange = worksheet->querySubObject("UsedRange");                             //获取该sheet的使用范围对象
    QAxObject * rows = usedrange->querySubObject("Rows");
    QAxObject * columns = usedrange->querySubObject("Columns");
    /*-------------------------------------------
     * 获取行数和列数
    ---------------------------------------------*/
    int rowStart = usedrange->property("Row").toInt();
    int columnStart = usedrange->property("Column").toInt();
    int columnCount = columns->property("Count").toInt();
    int rowCount = rows->property("Count").toInt();

    if(columnCount < 8)
    {
        QMessageBox msgBox( QMessageBox::Warning,QStringLiteral("错误信息"),
                            QStringLiteral("EXCEL文件格式不正确，请检查文件格式后重试。"),
                            QMessageBox::NoButton,
                            0
                            );
        msgBox.addButton(QStringLiteral("确定"), QMessageBox::AcceptRole);
        msgBox.exec();
        exit(0);
    }
    /*-------------------------------------------
     * 获取excel内容
    ---------------------------------------------*/
    QProgressDialog *progress = new QProgressDialog(QStringLiteral("创建Excel文件,正在导入人员信息到门禁系统中,请稍等...."), QStringLiteral("&取消"),0, rowCount-1,0);
    progress->setWindowModality(Qt::ApplicationModal);
    progress->setWindowTitle(QStringLiteral("导入记录"));
    progress->show();
    for (int i=rowStart; i < rowStart+rowCount; i++)                                      //行
    {
        if(i==rowStart)
        {
            //格式检测
        }else
        {
            ConsumerStruct consumers;
            consumers.certificateType = QStringLiteral("身份证");                                    //证件类型
            for (int j = columnStart; j < columnStart+columnCount; j++)
            {
                switch (j){
                case 1:
                {
                    QAxObject *cell = worksheet->querySubObject("Cells(int,int)",i,j);
                    consumers.certificateID = cell->dynamicCall("Value2()").toString();             //身份证号

                    QString certificateID = cell->dynamicCall("Value2()").toString();
                    QString datee = QStringLiteral("%1/%2/%3").arg(certificateID.mid(6,4)).arg(certificateID.mid(10,2)).arg(certificateID.mid(12,2));
                    if(QDate::fromString(datee,"yyyy/MM/dd").isValid())
                    {
                        consumers.birthday = datee;
                    }else
                    {
                        consumers.birthday = "";
                    }
                }break;
                case 2:
                {
                    QAxObject *cell = worksheet->querySubObject("Cells(int,int)",i,j);
                    consumers.consumerName = cell->dynamicCall("Value2()").toString();              //姓名
                }break;
                case 3:
                {
                    QAxObject *cell = worksheet->querySubObject("Cells(int,int)",i,j);
                    consumers.sex = cell->dynamicCall("Value2()").toString();                       //性别
                }break;
                case 4:
                {
                    QAxObject *cell = worksheet->querySubObject("Cells(int,int)",i,j);
                    consumers.nationality = cell->dynamicCall("Value2()").toString();               //民族
                }break;
                case 5:
                {
                    QAxObject *cell = worksheet->querySubObject("Cells(int,int)",i,j);
                    //consumers.birthday = cell->dynamicCall("Value2()").toString();                  //出生年月
                }break;
                case 6:
                {
                    QAxObject *cell = worksheet->querySubObject("Cells(int,int)",i,j);
                    consumers.addr = cell->dynamicCall("Value2()").toString();                      //地址
                }break;
                case 7:
                {
                    QAxObject *cell = worksheet->querySubObject("Cells(int,int)",i,j);
                    consumers.certificateUnit = cell->dynamicCall("Value2()").toString();           //签证机关
                }break;
                case 8:
                {
                    QAxObject *cell = worksheet->querySubObject("Cells(int,int)",i,j);
                    consumers.certificateDate = cell->dynamicCall("Value2()").toString();           //证件有效期
                }break;
                }
            }
            progress->setLabelText( QStringLiteral("正在导入%1，请稍等...").arg(consumers.consumerName) );
            progress->setValue(i);
            saveConsumer(consumers);
        }
    }
    progress->close();
    workbook->dynamicCall("Close (Boolean)", false);
    excel->dynamicCall("Quit (void)");
    delete excel;

    QMessageBox msgBox( QMessageBox::Warning,QStringLiteral("提示"),
                        QStringLiteral("用户信息已成功导入门禁系统。                "),
                        QMessageBox::NoButton,
                        0
                        );
    msgBox.addButton(QStringLiteral("确定"), QMessageBox::AcceptRole);
    msgBox.exec();
}
/*-------------------------------------------
 *
 * 保存客户信息
 *
---------------------------------------------*/
void ImportTool::saveConsumer(const ConsumerStruct &consumers)
{
    datadb->saveConsumerDb(consumers);
}
